package com.dudsbury.database;
/**
 * 	Database Connection Class
 * 
 * @author John Wright
 * @version 1.0
 *                                                                                                                                                                                                                                                                                                                                                                                                    
 */
import java.sql.Connection;




import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database implements DBInterface{

	private  String username, password, dbPath; 
	private  Connection connection;

	// Username, Password, database path eg "localhost/Assignment"
	public  void initialise(String user, String pass, String path) {

		username = user;
		password = pass;
		dbPath = path;


	}

	
	public  boolean connect() {

		try {
			connection = DriverManager
					.getConnection("jdbc:mysql://" + dbPath, username, password);

			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}
	
	
	public  boolean commit() {
		
		try {
			connection.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	// Performs a rollback on the database, going back to the previous commit
		public  boolean rollback() {
			
			try {
				connection.rollback();
				return true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
		}
	
	
	// Closes the connection to the database
	public  boolean close() {
		
		try {
			connection.close();
			return true;
		}
		catch(SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	// Executes an SQL statement where nothing is returned
	// returns whether successful or not
	public  boolean doSQL(String query) {
		connect();
		
		Statement stmt;

		try {
			stmt = connection.createStatement();
			stmt.executeUpdate(query);
			close();
			return true;
		}catch (SQLException e) {
			e.printStackTrace();
			close();
			return false;
		}
		
	}

	// Returns the result of a query as a 2D String array
	public  String[][] returnQuery(String query) {
		
		connect();
		
		StringBuilder output = new StringBuilder();
		Statement stmt;

		try {
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int rowCount = 0;

			while (rs.next()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					output.append(rs.getString(i) + "~");
				}
				output.append("\n");
				rowCount++;
			}

			String[][] result = new String[rs.getMetaData().getColumnCount()][rowCount];

			String s[] = output.toString().split("~");

			int i = 0;
			for (int y = 0; y < result[0].length; y++) {
				for (int x = 0; x < result.length; x++) {

					result[x][y] = s[i];
					i++;
				}
			}
			close();
			return result;
		} catch (SQLException e) {
			close();
			e.printStackTrace();
			return null;
		}
		
	}

	// Prints a query result in the form of a 2D String array to the console; Useful for debugging?
	public  void printQuery(String[][] s) {

		for (int y = 0; y < s[0].length; y++) {
			for (int x = 0; x < s.length; x++) {

				System.out.print(s[x][y] + "\t");
			}
			System.out.println();
		}

	}


	
	
	

	

}
